﻿using System;
using System.Globalization;
using System.IO;
using System.Text;
using System.Text.RegularExpressions;
using System.Web;
using System.Collections;
using System.Web.UI.WebControls;
using System.Xml;
using Microsoft.SharePoint;
using System.Data.SqlClient;
using System.Data;

namespace LifeInSharePoint.Common
{


    /// <summary>
    /// Provides common utilities for interacting with SharePoint lists.
    /// </summary>
    public static class SPListHelper
    {
        #region Constants and Fields

        /// <summary>
        /// The user link url.
        /// </summary>
        private const string UserLinkUrl = "_layouts/userdisp.aspx?ID={0}&Source={1}";

        public const int NonSiteUserId = -1;

        #endregion

        #region Public Methods

        /// <summary>
        /// Adds the specified <paramref name="file"/> to the root folder of the
        /// given <paramref name="list">list.</paramref>.
        /// </summary>
        /// <param name="file">
        /// The file to upload.
        /// </param>
        /// <param name="list">
        /// The destination list.
        /// </param>
        public static void AddFile(FileSystemInfo file, SPList list)
        {
            if (file == null)
            {
                throw new ArgumentNullException("file");
            }

            if (list == null)
            {
                throw new ArgumentNullException("list");
            }

            AddFileToList(file, list.RootFolder);
        }

        /// <summary>
        /// Adds the specified <paramref name="file"/> to the specified <paramref name="folder"/> of the
        /// given <paramref name="list">list.</paramref>.
        /// </summary>
        /// <param name="file">
        /// The file to upload.
        /// </param>
        /// <param name="list">
        /// The destination list.
        /// </param>
        /// <param name="folder">
        /// The folder to add the file to.
        /// </param>
        /// <param name="contentTypeName">
        /// Name of the content type for this document.
        /// </param>
        public static void AddFile(FileSystemInfo file, SPList list, SPFolder folder, string contentTypeName)
        {
            if (list == null)
            {
                throw new ArgumentNullException("list");
            }

            SPFile sharepointFile = AddFileToList(file, folder);
            sharepointFile.Item["ContentTypeId"] = list.ContentTypes[contentTypeName].Id;
            sharepointFile.Item.Update();
        }

        /// <summary>
        /// Ensures that the espcified field exists on a list. 
        /// It throws a meaningfull exception if the field doesn't exist
        /// instead of the usual SP "Value does not fall withing expected range" exception)
        /// </summary>
        /// <param name="list">
        /// </param>
        /// <param name="fieldName">
        /// </param>
        public static void EnsureFieldExists(SPList list, string fieldName)
        {
            if (list == null)
            {
                throw new ArgumentNullException("list");
            }

            if (!list.Fields.ContainsField(fieldName))
            {
                string errorMsg = String.Format(
                    CultureInfo.CurrentCulture, "List {0} does not contain field named {1}", list.Title, fieldName);
                Console.WriteLine(errorMsg);
                throw new ArgumentOutOfRangeException(errorMsg);
            }
        }

        /// <summary>
        /// The ensure field exists.
        /// </summary>
        /// <param name="web">
        /// The web.
        /// </param>
        /// <param name="listName">
        /// The list name.
        /// </param>
        /// <param name="fieldName">
        /// The field name.
        /// </param>
        /// <exception cref="ArgumentNullException">
        /// </exception>
        public static void EnsureFieldExists(SPWeb web, string listName, string fieldName)
        {
            if (web == null)
            {
                throw new ArgumentNullException("web");
            }

            SPList list = GetList(web, listName);
            EnsureFieldExists(list, fieldName);
        }

        /// <summary>
        /// The ensure field exists.
        /// </summary>
        /// <param name="item">
        /// The item.
        /// </param>
        /// <param name="fieldName">
        /// The field name.
        /// </param>
        /// <exception cref="ArgumentNullException">
        /// </exception>
        /// <exception cref="ArgumentOutOfRangeException">
        /// </exception>
        public static void EnsureFieldExists(SPListItem item, string fieldName)
        {
            if (item == null)
            {
                throw new ArgumentNullException("item");
            }

            if (!item.Fields.ContainsField(fieldName))
            {
                string errorMsg = String.Format(
                    CultureInfo.CurrentCulture,
                    "List {0} does not contain field named {1}",
                    item.ParentList.Title,
                    fieldName);
                Console.WriteLine(errorMsg);
                throw new ArgumentOutOfRangeException(errorMsg);
            }
        }

        /// <summary>
        /// Check if a list exists (unfortunatelly the only way to do this is to try to get the list)
        /// </summary>
        /// <param name="web">
        /// </param>
        /// <param name="listName">
        /// </param>
        public static void EnsureListExists(SPWeb web, string listName)
        {
            GetList(web, listName);
        }

        public static bool ListExists(this SPWeb web, string listName)
        {
            try
            {
                var list = web.Lists[listName];
                return true;
            }
            catch (ArgumentException)
            {
                return false;
            }
        }


        /// <summary>
        /// Safelly gets the int value of a SPListItem colum.
        /// </summary>
        /// <param name="item">
        /// </param>
        /// <param name="fieldName">
        /// </param>
        /// <returns>
        /// </returns>
        public static bool? GetColumnBoolValue(SPListItem item, string fieldName)
        {
            if (item == null)
            {
                throw new ArgumentNullException("item");
            }

            EnsureFieldExists(item, fieldName);

            object obj = item[fieldName];

            return Convert.ToBoolean(obj);
        }

        /// <summary>
        /// Safelly gets the double value of a SPListItem colum.
        /// </summary>
        /// <param name="item">
        /// </param>
        /// <param name="fieldName">
        /// </param>
        /// <returns>
        /// The get column double value.
        /// </returns>
        public static double GetColumnDoubleValue(SPListItem item, string fieldName)
        {
            if (item == null)
            {
                throw new ArgumentNullException("item");
            }

            EnsureFieldExists(item, fieldName);

            object obj = item[fieldName];

            if (obj != null)
            {
                return Convert.ToDouble(obj);
            }

            return 0;
        }

        /// <summary>
        /// Safelly gets the int value of a SPListItem colum.
        /// </summary>
        /// <param name="item">
        /// </param>
        /// <param name="fieldName">
        /// </param>
        /// <returns>
        /// The get column int value.
        /// </returns>
        public static int GetColumnIntValue(SPListItem item, string fieldName)
        {
            if (item == null)
            {
                throw new ArgumentNullException("item");
            }

            EnsureFieldExists(item, fieldName);

            object obj = item[fieldName];

            if (obj != null)
            {
                return Convert.ToInt32(obj);
            }

            return 0;
        }

        /// <summary>
        /// Safelly gets the int value of a SPListItem colum.
        /// </summary>
        /// <param name="item">
        /// </param>
        /// <param name="fieldName">
        /// </param>
        /// <returns>
        /// The get column int value.
        /// </returns>
        public static int GetColumnIntValue(DataRow item, string fieldName)
        {
            if (item == null)
            {
                throw new ArgumentNullException("item");
            }

            
            object obj = item[fieldName];

            if (obj != null)
            {
                return Convert.ToInt32(obj);
            }

            return 0;
        }


        /// <summary>
        /// Safelly gets the int value of a SPListItem colum.
        /// </summary>
        /// <param name="properties">
        /// </param>
        /// <param name="fieldName">
        /// </param>
        /// <returns>
        /// The get column int value.
        /// </returns>
        public static int GetColumnIntValue(SPItemEventDataCollection properties, string fieldName)
        {
            // EnsureFieldExists(item, fieldName);
            if (properties == null)
            {
                throw new ArgumentNullException("properties");
            }

            object obj = properties[fieldName];

            if (obj != null)
            {
                return Convert.ToInt32(obj);
            }

            return 0;
        }


        /// <summary>
        /// Safelly gets the string value of a SPListItem colum.
        /// </summary>
        /// <param name="item">
        /// </param>
        /// <param name="fieldName">
        /// </param>
        /// <returns>
        /// The get column string value.
        /// </returns>
        public static string GetColumnStringValue(DataRow item, string fieldName)
        {
            if (item == null)
            {
                throw new ArgumentNullException("item");
            }

            object obj = item[fieldName];

            if (obj != null)
            {
                return obj.ToString();
            }

            return String.Empty;
        }

        /// <summary>
        /// Safelly gets the string value of a SPListItem colum.
        /// </summary>
        /// <param name="item">
        /// </param>
        /// <param name="fieldName">
        /// </param>
        /// <returns>
        /// The get column string value.
        /// </returns>
        public static string GetColumnStringValue(SPListItem item, string fieldName)
        {
            if (item == null)
            {
                throw new ArgumentNullException("item");
            }

            EnsureFieldExists(item, fieldName);

            object obj = item[fieldName];

            if (obj != null)
            {
                return obj.ToString();
            }

            return String.Empty;
        }

        /// <summary>
        /// Safelly gets the string value of a properties colum.
        /// </summary>
        /// <param name="properties">
        /// </param>
        /// <param name="fieldName">
        /// </param>
        /// <returns>
        /// The get column string value.
        /// </returns>
        public static string GetColumnStringValue(SPItemEventDataCollection properties, string fieldName)
        {
            if (properties == null)
            {
                throw new ArgumentNullException("properties");
            }

            // EnsureFieldExists(item, fieldName);
            object obj = properties[fieldName];

            if (obj != null)
            {
                return obj.ToString();
            }

            return String.Empty;
        }

        /// <summary>
        /// Safelly gets the object value of a SPListItem colum.
        /// </summary>
        /// <param name="item">
        /// </param>
        /// <param name="fieldName">
        /// </param>
        /// <returns>
        /// The get column value.
        /// </returns>
        public static object GetColumnValue(SPListItem item, string fieldName)
        {
            if (item == null)
            {
                throw new ArgumentNullException("item");
            }

            EnsureFieldExists(item, fieldName);

            return item[fieldName];
        }

        /// <summary>
        /// Safelly gets the object value of a SPListItem colum.
        /// </summary>
        /// <param name="item">
        /// </param>
        /// <param name="fieldName">
        /// </param>
        /// <returns>
        /// The get column value.
        /// </returns>
        public static object GetColumnValue(DataRow item, string fieldName)
        {
            if (item == null)
            {
                throw new ArgumentNullException("item");
            }            
            return item[fieldName];
        }

        /// <summary>
        /// Safelly gets the Field details of a SPList.
        /// </summary>
        /// <param name="list">
        /// </param>
        /// <param name="fieldName">
        /// </param>
        /// <returns>
        /// </returns>
        public static SPField GetField(SPList list, string fieldName)
        {
            if (list == null)
            {
                throw new ArgumentNullException("list");
            }

            if (String.IsNullOrEmpty(fieldName))
            {
                throw new ArgumentNullException("fieldName");
            }

            EnsureFieldExists(list, fieldName);

            return list.Fields[fieldName];
        }

        /// <summary>
        /// Generate the url to the user or usergroup.
        /// </summary>
        /// <param name="userLink">
        /// </param>
        /// <returns>
        /// </returns>
        public static Uri GetLinkForUser(string userLink)
        {
            if (string.IsNullOrEmpty(userLink))
            {
                return null;
            }

            if (userLink.IndexOf(";#") > 0)
            {
                string id = userLink.Substring(0, userLink.IndexOf(";#"));
                string siteUrl = SPContext.Current.Site.ServerRelativeUrl;
                if (siteUrl == "/")
                {
                    siteUrl = string.Empty;
                }

                return
                    new Uri(
                        string.Format(
                            "{0}/{1}",
                            siteUrl,
                            string.Format(UserLinkUrl, id, HttpContext.Current.Request.Url.AbsolutePath)),
                        UriKind.Relative);
            }
            else
            {
                return null;
                //throw new DataSourceException(string.Format("{0} is not a valid SharePoint link value", userLink));
            }
        }

        /// <summary>
        /// Gets a list from an SPWeb. It throws a meaningfull error if the list does not exist.
        /// </summary>
        /// <param name="web">
        /// SPWeb containing the Lists collection.
        /// </param>
        /// <param name="listName">
        /// string Name of the list
        /// </param>
        /// <returns>
        /// true if it does exits; otherwise false.
        /// </returns>
        public static SPList GetList(SPWeb web, string listName)
        {
            SPVerificationHelper.VerifyObjectArgument(web, "web");
            SPVerificationHelper.VerifyStringArgument(listName, "listName");

            SPList list = TryGetList(web, listName);
            if (list == null)
            {
                throw new ArgumentException(
                    "Exception in trying to get list with name: '" + listName + "' from web '" + web.Title + "'");
            }

            return list;
        }


        public static string GetSPListItemCollectionAsXML(SPList list, SPQuery query)
        {
            string xml = string.Empty;

            if (null != list)
            {
                SPListItemCollection collection = list.GetItems(query);
                xml = collection.Xml;
            }
            else
            {
                throw new ArgumentException(
                    "Exception in trying to get list item collection from list : '" + list.Title);                
            }

            return xml;

        }

        /// <summary>
        /// Checks if a list item is approved
        /// </summary>
        /// <param name="item">
        /// </param>
        /// <returns>
        /// The item is approved.
        /// </returns>
        public static bool ItemIsApproved(SPListItem item)
        {
            if (item == null)
            {
                throw new ArgumentNullException("item");
            }

            if (item["_ModerationStatus"] != null && item["_ModerationStatus"].ToString() == "0")
            {
                return true;
            }

            return false;
        }

        /// <summary>
        /// Safelly sets the value of a list item
        /// </summary>
        /// <param name="item">
        /// </param>
        /// <param name="fieldName">
        /// </param>
        /// <param name="value">
        /// </param>
        public static void SetColumnValue(SPListItem item, string fieldName, object value)
        {
            if (item == null)
            {
                throw new ArgumentNullException("item");
            }

            EnsureFieldExists(item, fieldName);

            item[fieldName] = value;
        }

        /// <summary>
        /// The strip id.
        /// </summary>
        /// <param name="value">
        /// The value.
        /// </param>
        /// <returns>
        /// The strip id.
        /// </returns>
        public static string StripID(string value)
        {
            if (value == null)
            {
                return null;
            }

            return Regex.Replace(value, "^[0-9]+;#", string.Empty);
        }

        /// <summary>
        /// The strip id.
        /// </summary>
        /// <param name="value">
        /// The value.
        /// </param>
        /// <returns>
        /// The strip id.
        /// </returns>
        public static string Strip(string value)
        {
            if (value == null)
            {
                return null;
            }

            return Regex.Replace(value, "[0-9;#]", string.Empty);
        }

        /// <summary>
        /// Safelly gets the string value of a SPListItem colum.
        /// </summary>
        /// <param name="item">
        /// </param>
        /// <param name="fieldName">
        /// </param>
        /// <returns>
        /// The try get column string value.
        /// </returns>
        public static string TryGetColumnStringValue(SPListItem item, string fieldName)
        {
            if (item == null)
            {
                throw new ArgumentNullException("item");
            }

            // If item does not contain field then return null
            if (!item.Fields.ContainsField(fieldName))
            {
                return String.Empty;
            }

            object obj = item[fieldName];

            if (obj != null)
            {
                return obj.ToString();
            }

            return String.Empty;
        }



        public static bool ColumnValueIsUnique(SPItemEventProperties properties, string fieldName, string fieldType)
        {
            string TITLE_QUERY = @"<Where><Eq><FieldRef Name=""{1}"" /><Value Type=""{2}"">{0}</Value></Eq></Where>";
            if (properties.AfterProperties[fieldName] != null)
            {
                //get the title of the new item
                string currentTitle = properties.AfterProperties[fieldName].ToString();
                //get the web site object
                using (SPWeb web = properties.OpenWeb())
                {
                    //get the current list
                    SPList list = web.Lists[properties.ListId];
                    //query the list to check if there are items with the same title
                    SPQuery q = new SPQuery();
                    q.Query = string.Format(TITLE_QUERY, currentTitle, fieldName, fieldType);
                    SPListItemCollection itemsWithSameTitle = list.GetItems(q);
                    //if there are items, cancel the add, and show an error to the user.
                    if (itemsWithSameTitle.Count > 0)
                    {
                        return false;
                    }
                    else
                    {
                        return true;
                    }
                }
            }
            else
            {
                return true;
            }
        }
        /// <summary>
        /// Tries to get the value of a SPListItem colum. Returns the default for the epscified type
        /// if field does not exist.
        /// </summary>
        /// <typeparam name="T">
        /// </typeparam>
        /// <param name="item">
        /// </param>
        /// <param name="fieldName">
        /// </param>
        /// <returns>
        /// </returns>
        public static T TryGetColumnValue<T>(SPListItem item, string fieldName)
        {
            if (item == null)
            {
                throw new ArgumentNullException("item");
            }

            // If item does not contain field then return null
            if (!item.Fields.ContainsField(fieldName))
            {
                return default(T);
            }

            object obj = item[fieldName];

            if (obj == null)
            {
                return default(T);
            }

            return (T)Convert.ChangeType(obj, typeof(T));
        }

        /// <summary>
        /// Tries to get a list from an SPWeb. It returns null if the list does not exist.
        /// </summary>
        /// <param name="web">
        /// SPWeb containing the Lists collection.
        /// </param>
        /// <param name="listName">
        /// string Name of the list
        /// </param>
        /// <returns>
        /// SPList object or null if the list does not exist
        /// </returns>
        public static SPList TryGetList(SPWeb web, string listName)
        {
            SPVerificationHelper.VerifyObjectArgument(web, "web");
            SPVerificationHelper.VerifyStringArgument(listName, "listName");

            try
            {
                SPList list = web.Lists[listName];
                return list;
            }
            catch (Exception)
            {
                return null;
            }
        }




        /// <summary>
        /// Get the next available item id from a list
        /// </summary>
        /// < param name="site">site</param>
        /// < param name="listId">listId</param>
        /// < returns></returns>

        public static int GetNextItemID(SPSite site, Guid listId)
        {
            int id = -1;

            SPSecurity.RunWithElevatedPrivileges(delegate()
            {
                if (site.WebApplication.ContentDatabases.Count > 0)
                {
                    string dbConnString = site.WebApplication.ContentDatabases[0].DatabaseConnectionString;
                    SqlConnection connection = new SqlConnection(dbConnString);

                    try
                    {
                        connection.Open();
                        SqlCommand command = connection.CreateCommand();
                        command.CommandText = String.Format("select tp_NextAvailableId from AllLists where tp_ID = '{0}'", listId.ToString());
                        id = (int)command.ExecuteScalar();

                    }
                    finally
                    {
                        connection.Close();
                    }
                }
            });
            return id;

        }


        /// <summary>
        /// Get the id of a lookup field from lookup field properties string
        /// </summary>
        /// < param name="lookupField">site</param>
        /// < returns></returns>

        public static int GetLookupFieldIdFromPropertiesString(string lookupFieldProperties)
        {
            int result;

            if (!int.TryParse(lookupFieldProperties, out result))
            {
                string[] details = lookupFieldProperties.Split(new Char[] { ';', '#' });
                result = Convert.ToInt32(details[2]);
            }

            return result;

        }

        /// <summary>
        /// Get the UserId from properties string of a PeoplePicker field
        /// </summary>
        /// < param name="site">site</param>
        /// < returns></returns>

        public static int GetPeoplePickerIdFromPropertiesString(string peoplePickerFieldProperties)
        {
            int result;

            if (!int.TryParse(peoplePickerFieldProperties, out result))
            {
                string[] details = peoplePickerFieldProperties.Split(new Char[] { ';', '#' });
                result = Convert.ToInt32(details[0]);
            }

            return result;

        }






        #endregion

        #region Helper Methods

        /// <summary>
        /// Adds a file to the specified <paramref name="folder"/>.
        /// </summary>
        /// <param name="file">
        /// The file to add.
        /// </param>
        /// <param name="folder">
        /// The folder to add the file to.
        /// </param>
        /// <returns>
        /// The newly added file.
        /// </returns>
        private static SPFile AddFileToList(FileSystemInfo file, SPFolder folder)
        {
            SPFile newFile = folder.Files.Add(file.Name, File.ReadAllBytes(file.FullName));
            newFile.Update();

            return newFile;
        }

        /// <summary>
        /// Generate the url to the file.
        /// </summary>
        /// <param name="fileLink">
        /// </param>
        /// <returns>
        /// </returns>
        public static Uri GetLinkForFile(string fileLink)
        {
            if (string.IsNullOrEmpty(fileLink))
            {
                return null;
            }

            if (fileLink.IndexOf(";#") > 0)
            {
                return new Uri(SPContext.Current.Site.MakeFullUrl("/" + StripID(fileLink)));
            }
            else
            {
                return null;
                //throw newDataSourceException(string.Format("{0} is not a valid SharePoint file value", fileLink));
            }
        }

        /// <summary>
        /// Generate a link to a lookup item.
        /// </summary>
        /// <param name="lookupLink">
        /// </param>
        /// <returns>
        /// The get link for lookup.
        /// </returns>
        private static string GetLinkForLookup(string lookupLink)
        {
            if (lookupLink == null || lookupLink.IndexOf(";#") == 0)
            {
                //// TST - test.
                //throw new ICS.ShareWorkz.Website.ICS.Common.Exceptions.DataSourceException(
                //    string.Format("GetLinkForLookup: {0} is not a valid SharePoint link value", lookupLink));
            }

            return StripID(lookupLink);
        }

        /// <summary>
        /// Deletes all the items from the specifid list. Uses batch processing for performance.
        /// </summary>
        /// <param name="site">The site that hosts the list.</param>
        /// <param name="list">The name of the list that has the items to be deleted.</param>
        public static void DeleteAllItems(SPList list)
        {
            using (SPSite site = list.ParentWeb.Site)
            {
                StringBuilder sbDelete = BuildBatchDeleteCommand(list);

                site.RootWeb.ProcessBatchData(sbDelete.ToString());
            }
        }

        /// <summary>
        /// Builds a batch string with a list of all the items that are to be deleted.
        /// </summary>
        /// <param name="list">The list to delete all items from.</param>
        /// <returns></returns>
        private static StringBuilder BuildBatchDeleteCommand(SPList list)
        {
            StringBuilder sbDelete = new StringBuilder();
            sbDelete.Append("<?xml version=\"1.0\" encoding=\"UTF-8\"?>");
            sbDelete.Append("<Batch>");
            string command = "<Method><SetList Scope=\"Request\">" + list.ID + "</SetList><SetVar Name=\"ID\">{0}</SetVar><SetVar Name=\"Cmd\">Delete</SetVar></Method>";
            foreach (SPListItem item in list.Items)
            {
                sbDelete.Append(string.Format(command, item.ID.ToString()));
            }
            sbDelete.Append("</Batch>");
            return sbDelete;
        }

        private static SPListItem GetItemByUrl(string sUrl)
        {
            using (SPSite oSite = new SPSite(sUrl))
            {
                using (SPWeb oWeb = oSite.OpenWeb())
                {
                    //as far as I've seen, it always has a trailing slash, however...
                    int iLen = oWeb.Url.EndsWith("/") ? oWeb.Url.Length : oWeb.Url.Length + 1;

                    string sUrlEnd = sUrl.Substring(iLen);
                    sUrlEnd = sUrlEnd.Replace("%20", " ");

                    SPList oActualList = null;
                    foreach (SPList oList in oWeb.Lists)
                    {
                        if (sUrlEnd.StartsWith(oList.RootFolder.Url))
                        {
                            oActualList = oList;
                            break;
                        }
                    }
                    if (oActualList == null)
                    {
                        return null;
                    }

                    string[] sParts = (new Uri(sUrl)).Query.TrimStart('?').Split('&');
                    int iID = 0; //maybe there is no ID parameter in the query?
                    foreach (string sPart in sParts)
                    {
                        string[] sParamParts = sPart.Split('=');
                        if (string.Compare(sParamParts[0], "ID", true) == 0)
                        {
                            if (int.TryParse(sParamParts[1], out iID) == true)
                            {
                                break;  //we're sure we have found the ID and it is a number
                            }
                        }
                    }
                    if (iID > 0)
                    {
                        try
                        {
                            return oActualList.GetItemById(iID);
                        }
                        catch (ArgumentException ex)
                        {
                            return null;
                        }
                    }
                    else
                    {
                        return null;
                    }
                }
            }
        }
        #endregion


    }
}

